param ( [System.IO.DirectoryInfo]$RootScriptDir = (Get-Location).ToString()
      , $ServerName = "Cash-LT3"
			, $DatabaseName = "LibertySandbox"
			, [switch] $Force
      )

#region Helper Functions
# --------------------------------------------------------------------------------------------------------------------------
$RandomGenerator = New-Object System.Random

function Generate-RandomPassword ([int]$length=20)
{
	$NewPassword = ''
	1..$length | foreach { $NewPassword = $NewPassword + [char]$RandomGenerator.next(42,126) }
	$NewPassword
}

# --------------------------------------------------------------------------------------------------------------------------
function CreateDir ([System.IO.DirectoryInfo]$path)
{
	if (-not (Test-Path $path))
	{
		mkdir $path.FullName | Out-Null
	}
}	
# --------------------------------------------------------------------------------------------------------------------------
function Script-Line ($line)
{
	$line | Out-File $ScriptOptions.FileName  -Append
}

# --------------------------------------------------------------------------------------------------------------------------
function Script-FlowerBox([array]$lines)
{
	Script-Line "" 
	Script-Line "-- *****************************************************************************************************************" 
	foreach ($line in $lines)
	{
		Script-Line "-- $line" 
  }
	Script-Line "-- *****************************************************************************************************************" 
}

# --------------------------------------------------------------------------------------------------------------------------
function Script-Objects ($Collection, $Title, [switch]$NoTransaction)
{
	if ($Collection.Count -gt 0)
	{
		Script-FlowerBox @("$Title"); 
		if ($NoTransaction)
		{
			Script-Line "IF @@TRANCOUNT >0 COMMIT TRANSACTION;"
		}
		
    foreach ($item in ($Collection | ?{-not $_.IsSystemObject}))
		{
			$Scripter.Script($item)
		}
	}
}

# --------------------------------------------------------------------------------------------------------------------------
function Script-ObjectsToFiles ($Collection, [System.IO.DirectoryInfo]$scriptsDir, [switch]$NoTransaction)
{
	foreach ($dbObj in ($Collection | where{-not $_.IsSystemObject}))
	{
		CreateDir $scriptsDir
		$ScriptOptions.FileName = "$($scriptsDir.FullName)\$($dbObj.Schema).$($dbObj.Name).sql"
		
		Write-Host $ScriptOptions.FileName
		
		if ($NoTransaction)
		{
			Script-Line "IF @@TRANCOUNT >0 COMMIT TRANSACTION;"
		}

		$ScriptOptions.IncludeIfNotExists = $TRUE
		$ScriptOptions.ScriptDrops = $TRUE
		$Scripter.Script($dbObj)
		$ScriptOptions.IncludeIfNotExists = $FALSE
		$ScriptOptions.ScriptDrops = $FALSE
		$Scripter.Script($dbObj)
	}
}

function Set-ScriptOutput ([system.IO.FileInfo]$file)
{
	$ScriptOptions.FileName = $file.Fullname
	Write-Host $ScriptOptions.FileName
	if ($file.Exists) {	Remove-Item $ScriptOptions.FileName -Force }
}

function Generate-LoginScript($UserName)
{
	$ExecutionContext.InvokeCommand.ExpandString(@'
/* for security reasons the login is created disabled and with a random password. */
if NOT EXISTS (SELECT * FROM sys.server_principals where name = N'$UserName')
BEGIN
  CREATE LOGIN [$UserName] WITH PASSWORD=N'$(Generate-RandomPassword)', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=ON
  ALTER LOGIN [$UserName] DISABLE
END
GO

'@)
}

function Generate-Logins([array]$LoginNames)
{
	if ($LoginNames.Count -gt 0)
	{
		Script-FlowerBox @("Logins"); 
    foreach ($loginName in $LoginNames)
		{
			Script-Line (Generate-LoginScript $loginName)
		}
	}
}

#endregion

#region $DBConfigFileContent
$DBConfigFileContent = @'
<?xml version="1.0" encoding="utf-8" ?>
<DBFramework RootDirectory=".\Schema"
             Provider="SQLServer">
  <TargetEnvironments default="Local">
    <TargetEnvironment Name="Local"
                       ServerName="."
                       DatabaseName="DBSampleLocal"
                       />
    <TargetEnvironment Name="Dev"
                       ServerName="."
                       DatabaseName="DBSampleDev"
                       />
  </TargetEnvironments>
  <Scripts>
    <Script name="BeginTransaction" Type="SQL">
      SET XACT_ABORT ON
      SET TRANSACTION ISOLATION LEVEL READ COMMITTED
      GO
      BEGIN TRANSACTION;
    </Script>
    <Script name="EndTransaction" Type="SQL">
      IF @@ERROR &lt;&gt; 0 AND @@TRANCOUNT&lt;0 ROLLBACK TRANSACTION
      IF @@TRANCOUNT&gt;0 COMMIT TRANSACTION;
    </Script>
  </Scripts>
  <PatchGroups>
  <PatchGroups>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="Initialization" 
                Path="\InitialSetupScripts" 
                Pattern="((^.*\.sql$)|(^.*\.(?'schema'.*)\.(?'table'.*)\.table\.xml$))"
                DefaultExecute="False"
                >
      <FileHandlers>
        <FileHandler pattern="^.*\.sql$" Execution="SqlFile">
          <BeforeEach>
            <Execute Script="BeginTransaction"/>
          </BeforeEach>
          <AfterEach>
            <Execute Script="EndTransaction"/>
          </AfterEach>
        </FileHandler>
        <FileHandler pattern="^.*\.table\.xml$" Execution="GroupedXMLLoad">
          <BeforeAll>
            <Execute Script="BeginTransaction"/>
          </BeforeAll>
          <AfterAll>
            <Execute Script="EndTransaction"/>
          </AfterAll>
        </FileHandler>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="Patch" Path="\Patches" Pattern="((^.*\.sql$)|(^.*\.(?'schema'.*)\.(?'table'.*)\.table\.xml$))">
      <FileHandlers>
        <FileHandler pattern="^.*\.sql$" Execution="SqlFile">
          <BeforeEach>
            <Execute Script="BeginTransaction"/>
          </BeforeEach>
          <AfterEach>
            <Execute Script="EndTransaction"/>
          </AfterEach>
        </FileHandler>
        <FileHandler pattern="^.*\.table\.xml$" Execution="GroupedXMLLoad">
          <BeforeAll>
            <Execute Script="BeginTransaction"/>
          </BeforeAll>
          <AfterAll>
            <Execute Script="EndTransaction"/>
          </AfterAll>
        </FileHandler>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="Dataset" Path="\Datasets" Pattern="^(?'schema'.*)\.(?'table'.*)\.xml$">
      <FileHandlers>
        <FileHandler pattern="^.*\.xml$" Execution="GroupedXMLLoad"/>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="Functions" Path="\UserDefinedFunctions" Pattern="^(?'schema'.*)\.(?'name'.*)\.sql$">
      <FileHandlers>
        <FileHandler pattern="^.*\.sql$" Execution="SqlFile">
          <BeforeEach>
            <Execute Type="SQL">
              IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[$(schema)].[$(name)]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
              DROP FUNCTION [$(schema)].[$(name)]
            </Execute>
          </BeforeEach>
        </FileHandler>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="Views" Path="\Views" Pattern="^(?'schema'.*)\.(?'name'.*)\.sql$">
      <FileHandlers>
        <FileHandler pattern="^.*\.sql$" Execution="SqlFile">
          <BeforeEach>
            <Execute Type="SQL">
              IF (SELECT OBJECT_ID('[$(name)]','V')) IS NOT NULL
              DROP VIEW [$(schema)].[$(name)]
            </Execute>
          </BeforeEach>
        </FileHandler>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
    <PatchGroup Name="StoredProc" Path="\StoredProcedures" Pattern="^(?'schema'.*)\.(?'name'.*)\.sql$">
      <FileHandlers>
        <FileHandler pattern="^.*\.sql$" Execution="SqlFile">
          <BeforeEach>
            <Execute Type="SQL">
              IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[$(schema)].[$(name)]') AND type in (N'P', N'PC'))
              DROP PROCEDURE [$(schema)].[$(name)]
            </Execute>
          </BeforeEach>
        </FileHandler>
      </FileHandlers>
    </PatchGroup>
    <!-- ################################################################################################################## -->
  </PatchGroups>
</DBFramework>
'@
#endregion

# --------------------------------------------------------------------------------------------------------------------------
# Script Begins Here
# --------------------------------------------------------------------------------------------------------------------------
# Create SMO Server and DB Objects
# --------------------------------------------------------------------------------------------------------------------------

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO')  | out-null

$Server = new-object "Microsoft.SqlServer.Management.SMO.Server" "$ServerName"
$Database = $Server.Databases[$DatabaseName]

# --------------------------------------------------------------------------------------------------------------------------
# Create Scripting Object
# --------------------------------------------------------------------------------------------------------------------------
$Scripter = New-Object "Microsoft.SqlServer.Management.Smo.Scripter"
$Scripter.Server = $Server

$ScriptOptions = New-Object "Microsoft.SqlServer.Management.SMO.ScriptingOptions"
$ScriptOptions.AllowSystemObjects = $FALSE
$ScriptOptions.AppendToFile = $TRUE
$ScriptOptions.DriAll = $TRUE
$ScriptOptions.ClusteredIndexes = $TRUE
$ScriptOptions.Default = $TRUE
$ScriptOptions.DriAll = $TRUE
$ScriptOptions.ExtendedProperties = $TRUE
$ScriptOptions.IncludeIfNotExists = $FALSE
$ScriptOptions.IncludeDatabaseRoleMemberships = $TRUE
$ScriptOptions.IncludeHeaders = $FALSE
$ScriptOptions.Indexes = $TRUE
$ScriptOptions.IncludeHeaders = $FALSE
$ScriptOptions.FullTextIndexes = $TRUE
$ScriptOptions.NoCollation = $TRUE
$ScriptOptions.Permissions = $TRUE
$ScriptOptions.SchemaQualify = $TRUE
$ScriptOptions.SchemaQualifyForeignKeysReferences = $TRUE
$ScriptOptions.ScriptSchema = $TRUE
$ScriptOptions.ScriptDrops = $FALSE
$ScriptOptions.Triggers = $TRUE
$ScriptOptions.XmlIndexes = $TRUE
$ScriptOptions.ToFileOnly = $TRUE

$Scripter.Options = $ScriptOptions

# --------------------------------------------------------------------------------------------------------------------------
# Remove old directory if -Force
# --------------------------------------------------------------------------------------------------------------------------
if ($Force)
{
	if ($RootScriptDir.Exists)
	{
		if ($force)
		{
			Remove-Item "$($RootScriptDir.FullName)\*" -force -Recurse
		}
	}
	CreateDir $RootScriptDir
}

CreateDir "$($RootScriptDir.FullName)\Schema"
CreateDir "$($RootScriptDir.FullName)\Schema\InitialSetupScripts"
CreateDir "$($RootScriptDir.FullName)\Schema\InitialSetupScripts\01_Logins"
CreateDir "$($RootScriptDir.FullName)\Schema\InitialSetupScripts\02_UserRoles"
CreateDir "$($RootScriptDir.FullName)\Schema\Patches\00.00"
CreateDir "$($RootScriptDir.FullName)\Schema\Patches\01.00"


$ScriptOptions.IncludeIfNotExists = $TRUE
# --------------------------------------------------------------------------------------------------------------------------
Set-ScriptOutput "$($RootScriptDir.FullName)\Schema\InitialSetupScripts\02_UserRoles\01_CreateRoles.sql"

Script-Objects ($Database.Roles | ?{-not $_.IsFixedRole}) "Roles" -NoTransaction

# --------------------------------------------------------------------------------------------------------------------------
Set-ScriptOutput "$($RootScriptDir.FullName)\Schema\InitialSetupScripts\02_UserRoles\02_CreateUsers.sql"

# Script-Objects $Database.Users "Users"
$Users = $Database.Users | ?{(-not $_.IsSystemObject) -and ($_.LoginType -eq "SqlLogin") }
Script-Objects $Users "Users" -NoTransaction

# --------------------------------------------------------------------------------------------------------------------------
Set-ScriptOutput "$($RootScriptDir.FullName)\Schema\InitialSetupScripts\01_Logins\01_CreateLogins.sql"
$usernames = $Users | %{$_.Name}

Generate-Logins $usernames

# --------------------------------------------------------------------------------------------------------------------------
#$ScriptOptions.IncludeIfNotExists = $FALSE

Set-ScriptOutput "$($RootScriptDir.FullName)\Schema\Patches\00.00\003_InitialSchema.sql"
Script-Line "IF @@TRANCOUNT >0 COMMIT TRANSACTION;"
$SchemaExcludes = $Database.Users | %{$_.Name}
$SchemaExcludes += 'sys'
$SchemaExcludes += 'dbo'
Script-Objects ($Database.Schemas | ?{-not ($_.Name -match "^db_")} | ?{-not ($SchemaExcludes -contains $_.Name) }) "Schemas"

Script-Objects $Database.UserDefinedTypes "User Defined Types"
Script-Objects $Database.UserDefinedDataTypes "User Defined Datatypes"
Script-Objects $Database.UserDefinedAggregates "User Defined Aggregates"
Script-Objects $Database.UserDefinedTableTypes "User Defined Table Types"


if ($Database.Tables.Count -gt 0)
{
	Script-FlowerBox @("Tables"); 
	$Scripter.Script($Database.Tables)
}

# --------------------------------------------------------------------------------------------------------------------------
Script-ObjectsToFiles $Database.Views "$($RootScriptDir.FullName)\Schema\Views"
# --------------------------------------------------------------------------------------------------------------------------
Script-ObjectsToFiles $Database.StoredProcedures "$($RootScriptDir.FullName)\Schema\StoredProcedures"
# --------------------------------------------------------------------------------------------------------------------------
Script-ObjectsToFiles $Database.UserDefinedFunctions "$($RootScriptDir.FullName)\Schema\UserDefinedFunctions"
# --------------------------------------------------------------------------------------------------------------------------

$DBConfigFileContent | Set-Content "$RootScriptDir\DBDeploy.config"

exit

@"
IF NOT EXISTS (SELECT * FROM sys.server_principals where name = N'$UserName')
BEGIN
  CREATE LOGIN [$UserName] WITH PASSWORD=N'$Password', DEFAULT_DATABASE=[$DatabaseName], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
  ALTER LOGIN [$UserName] DISABLE
END
"@
